#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL SQL插入脚本对比器
用于对比两个INSERT脚本中的数据差异，输出不一致的行和列
"""

import re
import pandas as pd
from typing import List, Dict, Tuple, Any, Optional
import argparse
import sys
from datetime import datetime


class MySQLInsertComparator:
    def __init__(self):
        self.insert_pattern = re.compile(
            r'INSERT\s+INTO\s+`?(\w+)`?\s*\((.*?)\)\s*VALUES\s*(.*?)(?:;|$)',
            re.IGNORECASE | re.DOTALL
        )
        self.values_pattern = re.compile(r'\((.*?)\)', re.DOTALL)

    def parse_insert_script(self, script_content: str) -> Dict[str, List[Dict]]:
        """
        解析INSERT脚本，返回按表名分组的数据
        """
        tables_data = {}

        # 查找所有INSERT语句
        matches = self.insert_pattern.findall(script_content)

        for table_name, columns_str, values_str in matches:
            # 解析列名
            columns = [col.strip().strip('`').strip('"').strip("'")
                       for col in columns_str.split(',')]

            # 解析VALUES
            values_matches = self.values_pattern.findall(values_str)

            table_data = []
            for values_match in values_matches:
                # 解析单行数据
                row_data = self._parse_values(values_match)
                if len(row_data) == len(columns):
                    row_dict = dict(zip(columns, row_data))
                    table_data.append(row_dict)

            if table_name not in tables_data:
                tables_data[table_name] = []
            tables_data[table_name].extend(table_data)

        return tables_data

    def _parse_values(self, values_str: str) -> List[str]:
        """
        解析VALUES中的单行数据
        处理引号、NULL值等情况
        """
        values = []
        current_value = ""
        in_quotes = False
        quote_char = None
        i = 0

        while i < len(values_str):
            char = values_str[i]

            if not in_quotes:
                if char in ["'", '"']:
                    in_quotes = True
                    quote_char = char
                    current_value += char
                elif char == ',':
                    values.append(self._clean_value(current_value.strip()))
                    current_value = ""
                else:
                    current_value += char
            else:
                current_value += char
                if char == quote_char:
                    # 检查是否是转义引号
                    if i + 1 < len(values_str) and values_str[i + 1] == quote_char:
                        current_value += values_str[i + 1]
                        i += 1
                    else:
                        in_quotes = False
                        quote_char = None

            i += 1

        # 添加最后一个值
        if current_value.strip():
            values.append(self._clean_value(current_value.strip()))

        return values

    def _clean_value(self, value: str) -> str:
        """
        清理数据值，移除引号，处理NULL等
        """
        value = value.strip()

        if value.upper() == 'NULL':
            return None

        # 移除外层引号
        if ((value.startswith("'") and value.endswith("'")) or
                (value.startswith('"') and value.endswith('"'))):
            value = value[1:-1]
            # 处理转义引号
            value = value.replace("''", "'").replace('""', '"')

        return value

    def compare_tables(self, table1_data: List[Dict], table2_data: List[Dict],
                       table_name: str, primary_key: Optional[str] = None) -> List[Dict]:
        """
        对比两个表的数据
        """
        differences = []

        # 转换为DataFrame便于处理
        df1 = pd.DataFrame(table1_data) if table1_data else pd.DataFrame()
        df2 = pd.DataFrame(table2_data) if table2_data else pd.DataFrame()

        if df1.empty and df2.empty:
            return differences

        if df1.empty:
            for idx, row in df2.iterrows():
                differences.append({
                    'table': table_name,
                    'row_index': idx,
                    'type': 'missing_in_script1',
                    'column': 'ALL',
                    'script1_value': None,
                    'script2_value': dict(row)
                })
            return differences

        if df2.empty:
            for idx, row in df1.iterrows():
                differences.append({
                    'table': table_name,
                    'row_index': idx,
                    'type': 'missing_in_script2',
                    'column': 'ALL',
                    'script1_value': dict(row),
                    'script2_value': None
                })
            return differences

        # 确保列一致
        all_columns = set(df1.columns) | set(df2.columns)
        for col in all_columns:
            if col not in df1.columns:
                df1[col] = None
            if col not in df2.columns:
                df2[col] = None

        # 重新排序列
        df1 = df1.reindex(sorted(all_columns), axis=1)
        df2 = df2.reindex(sorted(all_columns), axis=1)

        # 如果指定了主键，按主键对比
        if primary_key and primary_key in df1.columns and primary_key in df2.columns:
            differences.extend(self._compare_by_primary_key(df1, df2, table_name, primary_key))
        else:
            # 按行索引对比
            differences.extend(self._compare_by_index(df1, df2, table_name))

        return differences

    def _compare_by_primary_key(self, df1: pd.DataFrame, df2: pd.DataFrame,
                                table_name: str, primary_key: str) -> List[Dict]:
        """
        按主键对比数据
        """
        differences = []

        # 获取所有主键值
        pk_values_1 = set(df1[primary_key].dropna())
        pk_values_2 = set(df2[primary_key].dropna())

        # 找出缺失的记录
        missing_in_2 = pk_values_1 - pk_values_2
        missing_in_1 = pk_values_2 - pk_values_1

        for pk_val in missing_in_2:
            row = df1[df1[primary_key] == pk_val].iloc[0]
            differences.append({
                'table': table_name,
                'primary_key': f"{primary_key}={pk_val}",
                'type': 'missing_in_script2',
                'column': 'ALL',
                'script1_value': dict(row),
                'script2_value': None
            })

        for pk_val in missing_in_1:
            row = df2[df2[primary_key] == pk_val].iloc[0]
            differences.append({
                'table': table_name,
                'primary_key': f"{primary_key}={pk_val}",
                'type': 'missing_in_script1',
                'column': 'ALL',
                'script1_value': None,
                'script2_value': dict(row)
            })

        # 对比共同的记录
        common_pk_values = pk_values_1 & pk_values_2
        differ_column = []
        for pk_val in common_pk_values:
            row1 = df1[df1[primary_key] == pk_val].iloc[0]
            row2 = df2[df2[primary_key] == pk_val].iloc[0]

            for column in df1.columns:
                val1 = row1[column]
                val2 = row2[column]

                # 处理None值比较
                if pd.isna(val1) and pd.isna(val2):
                    continue
                if pd.isna(val1) or pd.isna(val2) or str(val1) != str(val2):
                    differences.append({
                        'table': table_name,
                        'primary_key': f"{primary_key}={pk_val}",
                        'type': 'value_difference',
                        'column': column,
                        'script1_value': val1,
                        'script2_value': val2
                    })
                    differ_column.append(column)
        set_column= set(differ_column)
        print(f"数据不同的列：{set_column}", set_column)
        return differences

    def _compare_by_index(self, df1: pd.DataFrame, df2: pd.DataFrame,
                          table_name: str) -> List[Dict]:
        """
        按行索引对比数据
        """
        differences = []
        max_rows = max(len(df1), len(df2))

        for i in range(max_rows):
            if i >= len(df1):
                # script1中缺少此行
                row2 = df2.iloc[i]
                differences.append({
                    'table': table_name,
                    'row_index': i,
                    'type': 'missing_in_script1',
                    'column': 'ALL',
                    'script1_value': None,
                    'script2_value': dict(row2)
                })
            elif i >= len(df2):
                # script2中缺少此行
                row1 = df1.iloc[i]
                differences.append({
                    'table': table_name,
                    'row_index': i,
                    'type': 'missing_in_script2',
                    'column': 'ALL',
                    'script1_value': dict(row1),
                    'script2_value': None
                })
            else:
                # 对比每个字段
                row1 = df1.iloc[i]
                row2 = df2.iloc[i]

                for column in df1.columns:
                    val1 = row1[column]
                    val2 = row2[column]

                    # 处理None值比较
                    if pd.isna(val1) and pd.isna(val2):
                        continue
                    if pd.isna(val1) or pd.isna(val2) or str(val1) != str(val2):
                        differences.append({
                            'table': table_name,
                            'row_index': i,
                            'type': 'value_difference',
                            'column': column,
                            'script1_value': val1,
                            'script2_value': val2
                        })

        return differences

    def compare_scripts(self, script1_path: str, script2_path: str,
                        primary_keys: Optional[Dict[str, str]] = None) -> Dict[str, Any]:
        """
        对比两个SQL脚本文件
        """
        # 读取脚本文件
        try:
            with open(script1_path, 'r', encoding='utf-8') as f:
                script1_content = f.read()
        except UnicodeDecodeError:
            with open(script1_path, 'r', encoding='gbk') as f:
                script1_content = f.read()

        try:
            with open(script2_path, 'r', encoding='utf-8') as f:
                script2_content = f.read()
        except UnicodeDecodeError:
            with open(script2_path, 'r', encoding='gbk') as f:
                script2_content = f.read()

        # 解析脚本
        tables1 = self.parse_insert_script(script1_content)
        tables2 = self.parse_insert_script(script2_content)

        # 获取所有表名
        all_tables = set(tables1.keys()) | set(tables2.keys())

        all_differences = []
        summary = {
            'total_tables': len(all_tables),
            'tables_with_differences': 0,
            'total_differences': 0,
            'comparison_time': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        }

        for table_name in all_tables:
            table1_data = tables1.get(table_name, [])
            table2_data = tables2.get(table_name, [])

            primary_key = primary_keys.get(table_name) if primary_keys else None

            table_differences = self.compare_tables(
                table1_data, table2_data, table_name, primary_key
            )

            if table_differences:
                all_differences.extend(table_differences)
                summary['tables_with_differences'] += 1

        summary['total_differences'] = len(all_differences)

        return {
            'summary': summary,
            'differences': all_differences,
            'script1_tables': list(tables1.keys()),
            'script2_tables': list(tables2.keys())
        }

    def generate_report(self, comparison_result: Dict[str, Any],
                        output_file: Optional[str] = None) -> str:
        """
        生成对比报告
        """
        report_lines = []
        summary = comparison_result['summary']
        differences = comparison_result['differences']

        # 报告头部
        report_lines.append("=" * 80)
        report_lines.append("MySQL INSERT脚本对比报告")
        report_lines.append("=" * 80)
        report_lines.append(f"对比时间: {summary['comparison_time']}")
        report_lines.append(f"总表数: {summary['total_tables']}")
        report_lines.append(f"有差异的表数: {summary['tables_with_differences']}")
        report_lines.append(f"总差异数: {summary['total_differences']}")
        report_lines.append("")

        if not differences:
            report_lines.append("✅ 两个脚本完全一致，没有发现差异！")
        else:
            # 按表分组显示差异
            tables_with_diff = {}
            for diff in differences:
                table = diff['table']
                if table not in tables_with_diff:
                    tables_with_diff[table] = []
                tables_with_diff[table].append(diff)

            for table_name, table_diffs in tables_with_diff.items():
                report_lines.append(f"📋 表: {table_name}")
                report_lines.append("-" * 60)

                for i, diff in enumerate(table_diffs, 1):
                    if diff['type'] == 'missing_in_script1':
                        report_lines.append(f"  {i}. ❌ 脚本1中缺少记录")
                        if 'primary_key' in diff:
                            report_lines.append(f"     主键: {diff['primary_key']}")
                        elif 'row_index' in diff:
                            report_lines.append(f"     行索引: {diff['row_index']}")
                        report_lines.append(f"     脚本2值: {diff['script2_value']}")

                    elif diff['type'] == 'missing_in_script2':
                        report_lines.append(f"  {i}. ❌ 脚本2中缺少记录")
                        if 'primary_key' in diff:
                            report_lines.append(f"     主键: {diff['primary_key']}")
                        elif 'row_index' in diff:
                            report_lines.append(f"     行索引: {diff['row_index']}")
                        report_lines.append(f"     脚本1值: {diff['script1_value']}")

                    elif diff['type'] == 'value_difference':
                        report_lines.append(f"  {i}. 🔄 字段值不一致")
                        if 'primary_key' in diff:
                            report_lines.append(f"     主键: {diff['primary_key']}")
                        elif 'row_index' in diff:
                            report_lines.append(f"     行索引: {diff['row_index']}")
                        report_lines.append(f"     列名: {diff['column']}")
                        report_lines.append(f"     脚本1值: {diff['script1_value']}")
                        report_lines.append(f"     脚本2值: {diff['script2_value']}")

                    report_lines.append("")

                report_lines.append("")

        report_content = "\n".join(report_lines)

        # 保存到文件
        if output_file:
            with open(output_file, 'w', encoding='utf-8') as f:
                f.write(report_content)
            print(f"对比报告已保存到: {output_file}")

        return report_content


def main():

    path1 = "D:\\pycharmProject\\pythonProject\\dialog_nodes_v16_prod.sql"
    path2 = "D:\\pycharmProject\\pythonProject\\dialog_nodes_v17_prod.sql"
    primary_keys_path="D:\\pycharmProject\\pythonProject\\primary_keys.json"
    report_path = "D:\\pycharmProject\\pythonProject\\report.txt"

    # 解析主键配置
    primary_keys = None
    if primary_keys_path:
        import json
        try:
            with open(primary_keys_path, 'r', encoding='utf-8') as f:
                primary_keys = json.load(f)
        except Exception as e:
            print(f"无法读取主键配置文件: {e}")
            sys.exit(1)

    # 创建对比器
    comparator = MySQLInsertComparator()

    try:
        # 执行对比
        print("正在对比SQL脚本...")
        result = comparator.compare_scripts(path1, path2, primary_keys)

        # 生成报告
        report = comparator.generate_report(result, report_path)

        # 输出到控制台
        print(report)

        # 返回状态码
        if result['summary']['total_differences'] > 0:
            sys.exit(1)
        else:
            sys.exit(0)

    except Exception as e:
        print(f"错误: {e}")
        sys.exit(1)


if __name__ == "__main__":
    main()